<?php
namespace Category\Model;

use Zend\Db\ResultSet\ResultSet;
use Zend\Db\TableGateway\TableGateway;
use Zend\Db\Sql;
use Zend\Db\Sql\Where;
use Zend\Db\Sql\Select;
use Zend\Paginator\Adapter\DbSelect;
use Zend\Paginator\Paginator;
use Zend\Db\Sql\Predicate;
use Zend\Db\Sql\Expression;

class PaperCriculationTable
{
    protected $tableGateway;
	protected $select;
    public function __construct(TableGateway $tableGateway)
    {
        $this->tableGateway = $tableGateway;
		$this->select = new Select();
    }	
	public function addpaper($paper_names,$countPaper,$amt_paper,$details)	
    {
		$data = array(
			'paper_name'  => ucfirst($paper_names),	
			'district_id' 	 => $details['districts'],
			'area_id' 	     => $details['areas'],
			'count_paper' 	 => $countPaper,
			'location_id' 	 => $details['location'],
			'paper_id' 	     => $details['paper'],
			'amount_paper' 	 => $amt_paper,
			'status'         => 1,					
		);	
		if(isset($details['hid_paper']) && $details['hid_paper']!=""){
			$data1 = array(
				'updated_date' 	 => date('Y-m-d H:i:s'),
			 );
			 $data3= array_merge($data, $data1);
			$returnData=$this->tableGateway->update($data3, array('paper_cir_id' => $details['hid_paper']));				
		}else{
			 $data1 = array(
				'added_date' 	 => date('Y-m-d H:i:s'),		
				'updated_date' 	 => date('Y-m-d H:i:s'),
			 );
			  $data3=array_merge($data, $data1);
			$returnData=$this->tableGateway->insert($data3);
		}			
		return $returnData;
    }
	public function getpaperGrid($searchkey,$aColumns,$data,$distid,$areaid,$locationid,$paperId,$paginated=false)
	{
		$select = $this->tableGateway->getSql()->select();
		$select->join('clf_district', 'clf_paper_circulation.district_id=clf_district.district_id',array('d_status' =>new Expression('clf_district.status')),'left');
		$select->join('clf_area', 'clf_area.area_id=clf_paper_circulation.area_id',array('a_status' =>new Expression('clf_area.status')),'left');
		$select->join('clf_locations', 'clf_locations.location_id=clf_paper_circulation.location_id',array('l_status' =>new Expression('clf_locations.status')),'left');
		$select->join('clf_papers', 'clf_papers.paper_id=clf_paper_circulation.paper_id',array('p_status' =>new Expression('clf_papers.status')),'left');
		if($distid!='' && $areaid=='' &&  $locationid==''  && $paperId==''){ 	
			$select->where('clf_paper_circulation.district_id="'.$distid.'"');
			$select->where('clf_district.status=1');
		}else if($distid!='' && $areaid!='' && $locationid=='' && $paperId==''){
				$select->where('clf_paper_circulation.district_id="'.$distid.'"');
				$select->where('clf_paper_circulation.area_id="'.$areaid.'"');
				$select->where('clf_district.status=1');
				$select->where('clf_area.status=1');	
		}else if($distid!='' && $areaid!='' && $locationid!='' && $paperId==''){ 
			$select->where('clf_paper_circulation.district_id="'.$distid.'"');
			$select->where('clf_paper_circulation.area_id="'.$areaid.'"');
			$select->where('clf_paper_circulation.location_id="'.$locationid.'"');
			$select->where('clf_district.status=1');
			$select->where('clf_area.status=1');
			$select->where('clf_locations.status=1');
		}else if($distid!='' && $areaid!='' && $locationid!='' && $paperId!=''){ 
			$select->where('clf_paper_circulation.district_id="'.$distid.'"');
			$select->where('clf_paper_circulation.area_id="'.$areaid.'"');
			$select->where('clf_paper_circulation.location_id="'.$locationid.'"');
			$select->where('clf_paper_circulation.paper_id="'.$paperId.'"');
			$select->where('clf_district.status=1');
			$select->where('clf_area.status=1');
			$select->where('clf_locations.status=1');
			$select->where('clf_papers.status=1');
		}	
		// Search
		if(isset($data['sSearch'])){
			if($data['sSearch']!='0' && $data['sSearch']!='')
			{
				$select->where(array(
					new Predicate\PredicateSet(
						array(
						new Predicate\Like('paper_name', '%'.$data['sSearch'].'%'),
						),
							Predicate\PredicateSet::COMBINED_BY_OR
					),
				));
			}
		}
		// End		
		// Sort Order
		if(isset($data['iSortCol_0']))
		{
			$sOrder="";
			for($i=0;$i<intval($data['iSortingCols']);$i++)
			{
				if($data['bSortable_'.intval($data['iSortCol_'.$i])] == "true")
				{
					$sOrder.=$aColumns[intval($data['iSortCol_'.$i])]." ".$data['sSortDir_'.$i].",";
				}
			}
			$sOrder = substr_replace($sOrder,"", -1);
		}
		if($sOrder!=''){
			$select->order($sOrder);
		}
		// End		
		// Pagination
		if(isset($data['iDisplayStart']) && $data['iDisplayLength'] != '-1')
		{
			$start=(int)$data['iDisplayStart'];
			$currentPage = (int)$data['iDisplayLength'];
			$resultsPerPage = (int) $currentPage;
			$select->order('paper_cir_id DESC')->limit($currentPage)->offset($start);
		}
		// End		
		$resultSet = $this->tableGateway->selectWith($select);
		return $resultSet;
	}
	public function getcount($data='')
	{
		$select = $this->tableGateway->getSql()->select();
		if(isset($data['sSearch'])){
			if($data['sSearch']!='0' && $data['sSearch']!='')
			{
				$select->where(array(
					new Predicate\PredicateSet(
						array(
						new Predicate\Like('paper_name', '%'.$data['sSearch'].'%'),
						),
							Predicate\PredicateSet::COMBINED_BY_OR
					),
				));
			}
		}
		$resultSet = $this->tableGateway->selectWith($select);		
		return $resultSet->count();
	}
	public function changeStatus($distids,$value)
	{
		$data = array(
			'status'       =>$value,
		);
		$updateStatus=$this->tableGateway->update($data,array('(paper_cir_id IN ('.$distids.'))'));
		return 	$updateStatus;			
	}	
	public function getPaperInfo($paperid){
		$select = $this->tableGateway->getSql()->select();
		$select->where('paper_cir_id="'.$paperid.'"');
		$resultSet = $this->tableGateway->selectWith($select);
		return $resultSet->current();	  
	}
	public function getPaperList($dtId,$areaId,$locId){
		$select = $this->tableGateway->getSql()->select();
		$select->where('district_id="'.$dtId.'"');
		$select->where('area_id="'.$areaId.'"');
		$select->where('location_id="'.$locId.'"');
		$select->where('status="1"');
		$resultSet = $this->tableGateway->selectWith($select);
		return $resultSet;	
	}
}